from tortoise import BaseDBAsyncClient


async def upgrade(db: BaseDBAsyncClient) -> str:
    return """
        CREATE TABLE IF NOT EXISTS `rooms` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL UNIQUE COMMENT '房间名称',
    `sb` INT NOT NULL COMMENT '小盲注',
    `bb` INT NOT NULL COMMENT '大盲注',
    `max_players` INT NOT NULL COMMENT '最大玩家数',
    `pwd_hash` VARCHAR(128) COMMENT '房间密码哈希值',
    `status` VARCHAR(20) NOT NULL COMMENT '房间状态' DEFAULT 'waiting',
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    KEY `idx_rooms_name_e79737` (`name`)
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `hands` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `board_cards` VARCHAR(50) NOT NULL COMMENT '公共牌，逗号分隔',
    `pot_size` INT NOT NULL COMMENT '奖池大小',
    `finished_at` DATETIME(6) COMMENT '结束时间',
    `room_id` INT NOT NULL COMMENT '所属房间',
    CONSTRAINT `fk_hands_rooms_bd788834` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    `password_hash` VARCHAR(128) NOT NULL COMMENT '密码/哈希值',
    `avatar_url` VARCHAR(255) COMMENT '头像URL',
    `chips` INT NOT NULL COMMENT '筹码数量' DEFAULT 1000,
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    KEY `idx_users_usernam_266d85` (`username`)
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `actions` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `action_type` VARCHAR(20) NOT NULL COMMENT '行动类型，如fold, call, raise',
    `amount` INT NOT NULL COMMENT '行动涉及的筹码数',
    `timestamp` DATETIME(6) NOT NULL COMMENT '行动时间' DEFAULT CURRENT_TIMESTAMP(6),
    `hand_id` INT NOT NULL COMMENT '所属牌局',
    `user_id` INT NOT NULL COMMENT '所属用户',
    CONSTRAINT `fk_actions_hands_d7fce1ef` FOREIGN KEY (`hand_id`) REFERENCES `hands` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_actions_users_a920217a` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `chat_messages` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `message` LONGTEXT NOT NULL COMMENT '聊天消息内容',
    `timestamp` DATETIME(6) NOT NULL COMMENT '消息时间' DEFAULT CURRENT_TIMESTAMP(6),
    `room_id` INT NOT NULL COMMENT '所属房间',
    `user_id` INT NOT NULL COMMENT '发送用户',
    CONSTRAINT `fk_chat_mes_rooms_f1428098` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_chat_mes_users_91f55345` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `hand_players` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `hole_cards` VARCHAR(50) NOT NULL COMMENT '底牌，逗号分隔',
    `best_rank` VARCHAR(100) COMMENT '最佳牌型',
    `win_amount` INT NOT NULL COMMENT '赢得的筹码数' DEFAULT 0,
    `contributed_pot` INT NOT NULL COMMENT '贡献给奖池的筹码数' DEFAULT 0,
    `hand_id` INT NOT NULL COMMENT '所属牌局',
    `user_id` INT NOT NULL COMMENT '所属用户',
    UNIQUE KEY `uid_hand_player_hand_id_b35bcc` (`hand_id`, `user_id`),
    CONSTRAINT `fk_hand_pla_hands_757cf37e` FOREIGN KEY (`hand_id`) REFERENCES `hands` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_hand_pla_users_9b9c06c2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `room_players` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `seat_index` INT NOT NULL COMMENT '座位索引',
    `buy_in_chips` INT NOT NULL COMMENT '买入筹码',
    `left_chips` INT NOT NULL COMMENT '剩余筹码',
    `created_at` DATETIME(6) NOT NULL COMMENT '加入时间' DEFAULT CURRENT_TIMESTAMP(6),
    `left_at` DATETIME(6) COMMENT '离开时间',
    `room_id` INT NOT NULL COMMENT '所属房间',
    `user_id` INT NOT NULL COMMENT '所属用户',
    UNIQUE KEY `uid_room_player_room_id_e592ca` (`room_id`, `user_id`),
    UNIQUE KEY `uid_room_player_room_id_ff1b32` (`room_id`, `seat_index`),
    CONSTRAINT `fk_room_pla_rooms_787c78ee` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_room_pla_users_184908ff` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `transactions` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `delta` INT NOT NULL COMMENT '金币变动数，正数为增加，负数为减少',
    `type` VARCHAR(20) NOT NULL COMMENT '交易类型，如\'recharge\', \'buy_in\', \'win\', \'tax\'',
    `created_at` DATETIME(6) NOT NULL COMMENT '交易时间' DEFAULT CURRENT_TIMESTAMP(6),
    `ref_hand_id` INT COMMENT '关联的牌局（如有）',
    `user_id` INT NOT NULL COMMENT '所属用户',
    CONSTRAINT `fk_transact_hands_e1c7a6fd` FOREIGN KEY (`ref_hand_id`) REFERENCES `hands` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_transact_users_6189e5a9` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `aerich` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `version` VARCHAR(255) NOT NULL,
    `app` VARCHAR(100) NOT NULL,
    `content` JSON NOT NULL
) CHARACTER SET utf8mb4;"""


async def downgrade(db: BaseDBAsyncClient) -> str:
    return """
        """
